package com.css.fxfzdpzs.modules.dzzh.entity.repository;

import com.css.fxfzdpzs.modules.dzzh.entity.first.*;
import com.css.fxfzdpzs.modules.dzzh.entity.first.DzzhMapEarthquakeDisaster;
import com.css.fxfzdpzs.modules.dzzh.entity.second.EarthquakeDisasterNum;
import com.css.fxfzdpzs.modules.dzzh.param.ZoneParam;
import com.css.fxfzdpzs.modules.dzzh.vo.DzzhEvaluationTypeVO;
import com.css.fxfzdpzs.modules.dzzh.vo.SurfaceRuptureZoneVo;
import com.css.fxfzdpzs.modules.dzzh.vo.YesAndNo;
import com.css.fxfzdpzs.util.PlatformObjectUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.List;
import java.util.Map;

/**
 * @author y1fan
 * @description
 */
@Repository
public class DzzhRepositoryImpl implements DzzhRepository {

    @Resource
    JdbcTemplate jdbcTemplate;
    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    @Override
    public DzzhEarthquakeDisasterNum getDisasterNum(String code) {
        String sql = "select * from dzzh_earthquake_disaster_num where code ='"+code+"' ";
        List<DzzhEarthquakeDisasterNum> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DzzhEarthquakeDisasterNum.class));
        if (PlatformObjectUtils.isEmpty(list)){
            return null;
        }else {
            return list.get(0);
        }
    }

    @Override
    public List<EarthquakeDisasterNum> getProvinceDisasterNum(String code) {
        String sql = "select * from dzzh_earthquake_disaster_num  where 1=1 and code!='0'";
        if(PlatformObjectUtils.isNotEmpty(code)){
            String pcode = code.substring(0, 2);
            sql+=" and  code like '"+pcode+"%' and code!='"+code+"'" ;
        }else{
            sql+=" and  code like '__0000'";
        }
        sql+=" order by cast(code as int)";
        List<EarthquakeDisasterNum> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(EarthquakeDisasterNum.class));
        return list;
    }

    @Override
    public List<DzzhEarthquakeDisasterCount> getDisasterCount() {
        String sql = "select * from xczh_disaster_visits_count where system='4' ";
        List<DzzhEarthquakeDisasterCount> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DzzhEarthquakeDisasterCount.class));
        return list;
    }

    @Override
    public List<DzzhRiskAreaAnalysis> getRiskAreaAnalysis(String code) {
        String sql = "select * from dzzh_risk_area_analysis where code ='"+code+"'  order by  risk_area ";
        List<DzzhRiskAreaAnalysis> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DzzhRiskAreaAnalysis.class));
        return list;
    }

    @Override
    public List<DzzhEvaluationType> getMap(DzzhEvaluationTypeVO dzzhEvaluationTypeVO) {
        StringBuilder sql = new StringBuilder("select * from dzzh_evaluation_type ");
        StringBuilder whereSql = new StringBuilder(" where 1=1 ");

        if(!PlatformObjectUtils.isEmpty(dzzhEvaluationTypeVO.getEvaluationType())){
            whereSql.append(" and evaluation_type = '"+dzzhEvaluationTypeVO.getEvaluationType()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(dzzhEvaluationTypeVO.getSiteClassification())){
            whereSql.append(" and site_classification = '"+dzzhEvaluationTypeVO.getSiteClassification()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(dzzhEvaluationTypeVO.getSceEarthquake())){
            whereSql.append(" and sce_earthquake = '"+dzzhEvaluationTypeVO.getSceEarthquake()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(dzzhEvaluationTypeVO.getDesignIntensity())){
            whereSql.append(" and design_intensity = '"+dzzhEvaluationTypeVO.getDesignIntensity()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(dzzhEvaluationTypeVO.getMmBegin())&&!PlatformObjectUtils.isEmpty(dzzhEvaluationTypeVO.getMmEnd())){
            whereSql.append(" and   '"+dzzhEvaluationTypeVO.getMmBegin()+"'< mm   and  mm < '"+dzzhEvaluationTypeVO.getMmEnd()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(dzzhEvaluationTypeVO.getEarthquakeTimeBegin())&&!PlatformObjectUtils.isEmpty(dzzhEvaluationTypeVO.getEarthquakeTimeEnd())){
            whereSql.append(" and '"+dzzhEvaluationTypeVO.getEarthquakeTimeBegin()+"' < earthquake_time  and earthquake_time <'"+dzzhEvaluationTypeVO.getEarthquakeTimeEnd()+"' ");
        }
        List<DzzhEvaluationType> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DzzhEvaluationType.class));
        return list;
    }

    @Override
    public List<DzzhEarthquakeOverviewLandslide> getOverviewLandslide() {
        String sql = "select * from dzzh_earthquake_overview_landslide ";
        List<DzzhEarthquakeOverviewLandslide> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DzzhEarthquakeOverviewLandslide.class));
        return list;
    }

    @Override
    public List<DzzhEarthquakeOverviewLiquefying> getOverviewLiquefying() {
        String sql = "select * from dzzh_earthquake_overview_liquefying ";
        List<DzzhEarthquakeOverviewLiquefying> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DzzhEarthquakeOverviewLiquefying.class));
        return list;
    }

    @Override
    public List<DzzhEarthquakeOverviewSeismicSubsidence> getOverviewSeismicSubsidence() {
        String sql = "select * from dzzh_earthquake_overview_seismic_subsidence ";
        List<DzzhEarthquakeOverviewSeismicSubsidence> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DzzhEarthquakeOverviewSeismicSubsidence.class));
        return list;
    }

    @Override
    public List<DzzhEarthquakeOverviewSeismicSubsidence> getOverviewBreak() {
        String sql = "select * from dzzh_earthquake_overview_break ";
        List<DzzhEarthquakeOverviewSeismicSubsidence> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DzzhEarthquakeOverviewSeismicSubsidence.class));
        return list;
    }

    @Override
    public List<SurfaceRuptureZoneVo> getSurfaceRuptureZone(ZoneParam param) {
        StringBuilder sql=new StringBuilder("select * from dzzh_historical_surface");
        StringBuilder whereSql = new StringBuilder(" where 1=1 ");
        if (!PlatformObjectUtils.isEmpty(param.getStrTime())){
            whereSql.append(" and earth_quake_time >= '" + param.getStrTime() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getEndTime())){
            whereSql.append(" and earth_quake_time <= '" + param.getEndTime() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getStrRegion())){
            whereSql.append(" and  earth_quake_level >='"+param.getStrRegion()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getEndRegion())){
            whereSql.append(" and  earth_quake_level <='"+param.getEndRegion()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(param.getProvince())){
            whereSql.append(" and province = '"+param.getProvince()+"'");
        }
        List<SurfaceRuptureZoneVo> list = jdbcTemplate.query(sql.append(whereSql).toString(), new BeanPropertyRowMapper<>(SurfaceRuptureZoneVo.class));
        return list;
    }

    @Override
    public DzzhCountAreaResult getDisasterOverview(String disasterType, String secondType,String code,Integer i) {
        String sql="";
        if (disasterType.equals("地震滑坡-逻辑斯蒂模型")){
          sql = "select * from dzzh_count_area_result where model_type ='"+disasterType+"' and dz_intensity ='"+secondType+"' and code ='"+code+"'  and dp_flag='1' and  gridvalue="+i+" ";
        }else if (disasterType.equals("砂土液化-层次分析法")){
            sql="select * from dzzh_count_area_result where model_type ='"+disasterType+"'  and code ='"+code+"'  and dp_flag='1' and gridvalue='"+secondType+"' ";
        }else if (disasterType.equals("软土震陷")){
            sql="select * from dzzh_count_area_result where model_type ='"+disasterType+"'  and code ='"+code+"'  and dp_flag='1' and dz_intensity='"+secondType+"' and gridvalue='"+i+"' ";
        }
        DzzhCountAreaResult result = null;
        try {
            result = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(DzzhCountAreaResult.class));
            return result;
        } catch (DataAccessException e) {
          return null;
        }

    }

    @Override
    public YesAndNo getYesAndNo(String s, String weakSecondType,String code) {
        String sql = "select * from dzzh_assess_type where disaster_type ='"+s+"' and second_type ='"+weakSecondType+"' and code ='"+code+"' ";
        List<YesAndNo> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(YesAndNo.class));
        if (list.size()==0){
            return null;
        }else {
            return list.get(0);
        }
    }

    @Override
    public List<DzzhMapEarthquakeDisaster>  getOtherTotleNum(String type, String provinceCode) {
        StringBuilder sql=new StringBuilder();
        //一级页面集合
        if (PlatformObjectUtils.isEmpty(provinceCode)){
            sql.append("select * from ( ");
            sql.append("SELECT sum(num) as num,type ,province_code as provinceCode FROM dzzh_map_earthquake_disaster ");
            sql.append(" where 1=1  and  type='"+type+"'");
            sql.append(" group by province_code,type");
            sql.append(" ) a left join dzzh_map_dict b on a.provinceCode=b.province_code");
        }else {
            //二级页面集合
            sql.append("SELECT * FROM dzzh_map_earthquake_disaster where 1=1 ");
            sql.append(" and  province_code ='"+provinceCode+"' and type='"+type+"'");
        }
        List<DzzhMapEarthquakeDisaster> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DzzhMapEarthquakeDisaster.class));
        return list;
    }

    @Override
    public List<Map<String, Object>> getOneTotleNum(String provinceCode) {
        StringBuilder sql=new StringBuilder("SELECT type,sum(num) as num FROM dzzh_map_earthquake_disaster");
        StringBuilder whereSql = new StringBuilder(" where 1=1 ");
        if (!PlatformObjectUtils.isEmpty(provinceCode)){
            whereSql.append(" and  province_code ='"+provinceCode+"'");
        }
        whereSql.append(" group by type");
        List<Map<String, Object>> stringObjectMap = jdbcTemplate.queryForList(sql.append(whereSql).toString());
        return stringObjectMap;
    }

    @Override
    public List<LogisticGrowthEntity> getResultLogisticGrowth(String dzIntensity) {
        StringBuilder sql=new StringBuilder("select * from dzzh_result_logistic_growth ");
        StringBuilder whereSql = new StringBuilder(" where 1=1 and dp_flag='0' and file_path like '逻辑斯蒂模型_计算_%'  ");
        if (!PlatformObjectUtils.isEmpty(dzIntensity)){
            whereSql.append(" and  dz_intensity ='"+dzIntensity+"'");
        }
        List<LogisticGrowthEntity> list = jdbcTemplate.query(sql.append(whereSql).toString(), new BeanPropertyRowMapper<>(LogisticGrowthEntity.class));
        return list;
    }

    @Override
    public List<SeimicLandslideEntity> getSeismicLandslide(String type, String dzIntensity,String code) {
        StringBuilder sql=new StringBuilder("select gridvalue,name,sum(area) as area,sum(area)/total_area*100 as percent from dzzh_count_area_result  ");
        StringBuilder whereSql = new StringBuilder(" where 1=1 and  model_type='地震滑坡-逻辑斯蒂模型'  and dp_flag='1' ");
        if (!PlatformObjectUtils.isEmpty(type)){
            whereSql.append(" and  type ='"+type+"'");
        }
        if (!PlatformObjectUtils.isEmpty(dzIntensity)){
            whereSql.append(" and  dz_intensity ='"+dzIntensity+"'");
        }
        if (!PlatformObjectUtils.isEmpty(code)){
            whereSql.append(" and  code like '"+code+"%'");
        }
        whereSql.append("GROUP BY gridvalue,name,total_area order by gridvalue,name");
        List<SeimicLandslideEntity> list = jdbcTemplate.query(sql.append(whereSql).toString(), new BeanPropertyRowMapper<>(SeimicLandslideEntity.class));
        return list;
    }

    @Override
    public List<SeimicLandslideEntity> getSandLiquefaction(String type, String dzIntensity,String code) {
        StringBuilder sql=new StringBuilder("select gridvalue,name,sum(area) as area,sum(area)/total_area*100 as percent from dzzh_count_area_result  ");
        StringBuilder whereSql = new StringBuilder(" where 1=1 and  model_type='砂土液化-层次分析法' and extend1='1' and dp_flag='1' ");
        if (!PlatformObjectUtils.isEmpty(type)){
            whereSql.append(" and  type ='"+type+"'");
        }
        if (!PlatformObjectUtils.isEmpty(dzIntensity)){
            whereSql.append(" and  dz_intensity ='"+dzIntensity+"'");
        }
        if (!PlatformObjectUtils.isEmpty(code)){
            whereSql.append(" and  code like '"+code+"%'");
        }
        whereSql.append("GROUP BY gridvalue,name,total_area order by gridvalue,name");
        List<SeimicLandslideEntity> list = jdbcTemplate.query(sql.append(whereSql).toString(), new BeanPropertyRowMapper<>(SeimicLandslideEntity.class));
        return list;
    }

    @Override
    public List<SeimicLandslideEntity> getSoftEarth(String type, String dzIntensity,String code) {
        StringBuilder sql=new StringBuilder("select gridvalue,name,sum(area) as area,sum(area)/total_area*100 as percent from dzzh_count_area_result  ");
        StringBuilder whereSql = new StringBuilder(" where 1=1 and  model_type='软土震陷'  and dp_flag='0' ");
        if (!PlatformObjectUtils.isEmpty(type)){
            whereSql.append(" and  type ='"+type+"'");
        }
        if (!PlatformObjectUtils.isEmpty(dzIntensity)){
            whereSql.append(" and  dz_intensity ='"+dzIntensity+"'");
        }
        if (!PlatformObjectUtils.isEmpty(code)){
            whereSql.append(" and  code like '"+code+"%'");
        }
        whereSql.append("GROUP BY gridvalue,name,total_area order by gridvalue,name");
        List<SeimicLandslideEntity> list = jdbcTemplate.query(sql.append(whereSql).toString(), new BeanPropertyRowMapper<>(SeimicLandslideEntity.class));
        return list;
    }


    @Override
    public void delEarthquakeDisaster() {
        String sql = "delete from dzzh_earthquake_disaster_num";
        jdbcTemplate.update(sql);
    }

    @Override
    public void saveEarthquakeDisaster(List<EarthquakeDisasterNum> entityList) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(entityList.toArray());
        namedParameterJdbcTemplate.batchUpdate("INSERT INTO dzzh_earthquake_disaster_num(id,landslide_num,liquefying_num,seismic_subsidence_num,break_num,code,name) values (:id,:LandslideNum,:LiquefyingNum,:SeismicSubsidenceNum,:BreakNum,:code,:name)", beanSources);

    }

    @Override
    public void delProvinceDisaster() {
        String sql = "delete from dzzh_province_disaster_num";
        jdbcTemplate.update(sql);
    }

    @Override
    public void saveProvinceDisaster(List<DzzhProvinceDisasterNum> provinceDisasterNums) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(provinceDisasterNums.toArray());
        namedParameterJdbcTemplate.batchUpdate("INSERT INTO dzzh_province_disaster_num(id, province, province_landslide_num,province_liquefying_num,province_seismic_subsidence_num,province_break_num, code) values (:id,:province,:provinceLandslideNum,:provinceLiquefyingNum,:provinceSeismicSubsidenceNum,:provinceBreakNum,:code)", beanSources);
    }

    @Override
    public void delMapEarthquakeDisaster() {
        jdbcTemplate.update("delete from dzzh_map_earthquake_disaster");
    }

    @Override
    public void saveMapEarthquakeDisaster(List<DzzhMapEarthquakeDisaster> resultList) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(resultList.toArray());
        namedParameterJdbcTemplate.batchUpdate("insert into dzzh_map_earthquake_disaster(id,province_code,city,city_code,num,type) values(:id,:provinceCode,:city,:cityCode,:num,:type)", beanSources);
    }

    @Override
    public void save(SurfaceRuptureZoneVo item) {
        jdbcTemplate.update("insert into dzzh_historical_surface  (id,province,region,longitude,latitude,earth_quake_level,rupture_length ,earth_quake_time,geom)" +
                " values ('" + item.getId() + "', '" + item.getProvince() + "', '" + item.getRegion() + "', '" + item.getLongitude() + "', '" + item.getLatitude() + "', '" + item.getEarthQuakeLevel() + "', '" + item.getRuptureLength() + "', '" + item.getEarthQuakeTime() + "',st_geomfromtext('POINT(' || " + item.getLongitude() + " || ' ' || " + item.getLatitude() + " || ')', 4490)) ");
    }

    @Override
    public void delete() {
        String sql = "delete from dzzh_historical_surface";
        jdbcTemplate.update(sql);
    }
}
